//
//  ARC_DB.m
//  SQLiteTesting
//
//  Created by kitinat on 1/23/13.
//  Copyright (c) 2013 EGA. All rights reserved.
//

#import "EGAAppDelegate.h"
#import "ARC_DB.h"
#import <sqlite3.h>
#import "EGAarcAssignCell.h"
#import "EGAUtility.h"
@implementation ARC_DB

sqlite3 * DB;
sqlite3_stmt * STMT;

- (BOOL)internetConnected
{
    NSString *URLString = [NSString stringWithContentsOfURL:[NSURL URLWithString:@"http://164.115.5.192/archive/"]];
    return ( URLString != NULL ) ? YES : NO;
}


#pragma init and close db
-(void)initialDB
{
   // NSString * strProjDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"test2.sqlite"];
    
    //get path of file in document
    NSArray  * arrDocDBPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES);
    NSString * documentsDirectory = [arrDocDBPath objectAtIndex:0];
      self._dbPath = [[NSBundle mainBundle]pathForResource:@"test_a3"ofType:@"sqlite"];
    NSString * strDocDBPath = [documentsDirectory stringByAppendingPathComponent:@"test_a3.sqlite"];
    
    //if file not exist in document, then copy from project to document
    NSFileManager *fileManager = [NSFileManager defaultManager];
    if(![fileManager fileExistsAtPath:strDocDBPath])
    {
        [[NSFileManager defaultManager] copyItemAtPath: self._dbPath toPath:strDocDBPath error:nil];
    }
    const char * strDocDBPathUTF8 = [strDocDBPath UTF8String];
    
    //open db
    sqlite3_open(strDocDBPathUTF8, &DB);
}

-(void)closeDB
{
    //clean up
    sqlite3_finalize(STMT);
    
    //close db
    sqlite3_close(DB);
}


#pragma user
-(void) saveUserData:(NSString*)EmpID
              RoleID:(NSString*)RoleID
            RoleName:(NSString*)RoleName
               Title:(NSString*)Title
         FirstNameTH:(NSString*)FirstNameTH
          LastNameTH:(NSString*)LastNameTH
          SectorName:(NSString*)SectorName
           IsHeadEGA:(NSString*)IsHeadEGA
{    
    [self initialDB];
    
    //at first delete old data
    NSString * strDeleteStatement = [NSString stringWithFormat:@"DELETE FROM User"];
    const char * consCharDeleteStatement = [strDeleteStatement UTF8String];
    sqlite3_prepare_v2(DB, consCharDeleteStatement, -1, &STMT, NULL);
    sqlite3_step(STMT);
    
    //and then insert parameter to database
    NSString * strInsertStatement = [NSString stringWithFormat:@"INSERT INTO User (EmpID,RoleID,RoleName,Title,FirstNameTH,LastNameTH,SectorName,IsHeadEGA) VALUES ('%@', '%@','%@', '%@', '%@', '%@', '%@', '%@')",EmpID,RoleID,RoleName,Title,FirstNameTH,LastNameTH,SectorName,IsHeadEGA];
    const char * consCharInsertStatement = [strInsertStatement UTF8String];
    sqlite3_prepare_v2(DB, consCharInsertStatement, -1, &STMT, NULL);
    
    //run (step)
    sqlite3_step(STMT);
    
    [self closeDB];
}
-(NSDictionary*) getUserData
{
    [self initialDB];
    
    NSDictionary * returnDict = [[NSDictionary alloc] init];
    
    //prepare statement
    const char * queryStatement = [@"select * from User" UTF8String];
    sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
    
    //fetch to dict
    while ((sqlite3_step(STMT)) == SQLITE_ROW) {
        NSString *EmpID         = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 0)];
        NSString *RoleID        = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 1)];
        NSString *RoleName      = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 2)];
        NSString *Title         = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 3)];
        NSString *FirstNameTH   = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 4)];
        NSString *LastNameTH    = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 5)];
        NSString *SectorName    = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 6)];
        NSString *IsHeadEGA     = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 7)];

        
        returnDict = [[NSDictionary alloc] initWithObjectsAndKeys:
                                        EmpID, @"EmpID",
                                        RoleID, @"RoleID",
                                        RoleName, @"RoleName",
                                        Title, @"Title",
                                        FirstNameTH, @"FirstNameTH",
                                        LastNameTH, @"LastNameTH",
                                        SectorName, @"SectorName",
                                        IsHeadEGA, @"IsHeadEGA",
                                        nil];
    }
    
    [self closeDB];
    
    return returnDict;
}

-(BOOL)isHasUserInfoDB//yok
{
    [self initialDB];
    //prepare statement
    const char * queryStatement = [@"SELECT * FROM User " UTF8String];
    sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
    BOOL chk=NO;
    while ((sqlite3_step(STMT)) == SQLITE_ROW) {
        chk=YES;
    }
    [self closeDB];
    return chk;
    
}








#pragma book
-(BOOL)isHasBookInDB
{
    [self initialDB];
        
    //prepare statement
    const char * queryStatement = [@"SELECT * FROM ListBook" UTF8String];
    sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
    
    //fetch each to dict and add to arr
    int RowNum = 0;
    while ((sqlite3_step(STMT)) == SQLITE_ROW) {
        RowNum += 1;
    }
    
    [self closeDB];
 //  NSLog(@"RowNum:%d",RowNum);
    
    if (RowNum == 0)
    {
        //don't have data
        return NO;
      
    }
    else
    {
        // have data
        return YES;
    }
}
-(void)deleteAllBook
{
    [self initialDB];
    
    //check internet access
    if([self internetConnected])
    {
        
        //at first delete old data
        NSString * strDeleteStatement = [NSString stringWithFormat:@"DELETE FROM ListBook"];
        const char * consCharDeleteStatement = [strDeleteStatement UTF8String];
        sqlite3_prepare_v2(DB, consCharDeleteStatement, -1, &STMT, NULL);
        sqlite3_step(STMT);
    }
    [self closeDB];
    
    
}
-(void) saveAllBook
{
    [self initialDB];
    
    //check internet access
    if([self internetConnected])
    {
        //        //at first delete old data
        //        NSString * strDeleteStatement = [NSString stringWithFormat:@"DELETE FROM ListBook"];
        //        const char * consCharDeleteStatement = [strDeleteStatement UTF8String];
        //        sqlite3_prepare_v2(DB, consCharDeleteStatement, -1, &STMT, NULL);
        //        sqlite3_step(STMT);
        
        //get json into array
        NSMutableArray * arrOfDict = [[NSMutableArray alloc] init];
        
        NSString *jsonURL=[NSString stringWithFormat:@"http://%@ListBook.ashx?search=",serviceUrl];
        
        NSData *json = [NSData dataWithContentsOfURL:[NSURL URLWithString:jsonURL]];
        
        
        arrOfDict = [NSJSONSerialization JSONObjectWithData:json options:kNilOptions error:nil];
        //insert json arr to DB
        sqlite3_step(STMT);
        NSInteger row=0;
        for(NSDictionary *dictForEachRow in arrOfDict)
        {
            row=row+1;
            NSString * ID = [dictForEachRow objectForKey:@"ID"];
            NSString * Doc_FiscalYear = [dictForEachRow objectForKey:@"Doc_FiscalYear"];
            NSString * Doc_Register = [dictForEachRow objectForKey:@"Doc_Register"];
            NSString * Doc_Serial = [dictForEachRow objectForKey:@"Doc_Serial"];
            NSString * Doc_Subject = [dictForEachRow objectForKey:@"Doc_Subject"];
            NSString * Doc_Description = [dictForEachRow objectForKey:@"Doc_Description"];
            NSString * Doc_date = [dictForEachRow objectForKey:@"Doc_date"];
            NSString * Doc_ReceiveDate = [dictForEachRow objectForKey:@"Doc_ReceiveDate"];
            NSString * Doc_to = [dictForEachRow objectForKey:@"Doc_to"];
            NSString * DocToName = [dictForEachRow objectForKey:@"DocToName"];
            NSString * Org_Name = [dictForEachRow objectForKey:@"Org_Name"];
            NSString * Doc_Priority_ID = [dictForEachRow objectForKey:@"Doc_Priority_ID"];
            NSString * Doc_Priority_Name = [dictForEachRow objectForKey:@"Doc_Priority_Name"];
            NSString * Doc_Secret_ID = [dictForEachRow objectForKey:@"Doc_Secret_ID"];
            NSString * Doc_Status_ID = [dictForEachRow objectForKey:@"Doc_Status_ID"];
            NSString * Doc_Status_Name = [dictForEachRow objectForKey:@"Doc_Status_Name"];
            
            NSString * empEmail = [dictForEachRow objectForKey:@"empEmail"];
            NSString * endDate = [dictForEachRow objectForKey:@"endDate"];
            NSString * startDate = [dictForEachRow objectForKey:@"startDate"];
            NSString * taskDetail = [dictForEachRow objectForKey:@"taskDetail"];
            NSString * IsDueDate = [dictForEachRow objectForKey:@"IsDueDate"];
            NSString * DocPriorityID = [dictForEachRow objectForKey:@"DocPriorityID"];
            NSString * chkAssign = [dictForEachRow objectForKey:@"chkAssign"];
            
            NSString * DocBookBeforeSign_ID = [dictForEachRow objectForKey:@"DocBookBeforeSign_ID"];
            NSString * DocBookBeforeSign_Name = [dictForEachRow objectForKey:@"DocBookBeforeSign_Name"];
            NSString * DocAttachment_ID = [dictForEachRow objectForKey:@"DocAttachment_ID"];
            NSString * DocAttachment_Name = [dictForEachRow objectForKey:@"DocAttachment_Name"];

            
            
            //
            NSString * strInsertStatement = [NSString stringWithFormat:@"INSERT INTO ListBook (ID, Doc_FiscalYear, Doc_Register, Doc_Serial, Doc_Subject, Doc_Description, Doc_date, Doc_ReceiveDate, Doc_to, DocToName, Org_Name, Doc_Priority_ID, Doc_Priority_Name, Doc_Secret_ID, Doc_Status_ID, Doc_Status_Name,empEmail,endDate,startDate,taskDetail,IsDueDate,DocPriorityID,chkAssign,DocBookBeforeSign_ID,DocBookBeforeSign_Name,DocAttachment_ID,DocAttachment_Name) VALUES ('%@', '%@','%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@')", ID, Doc_FiscalYear, Doc_Register, Doc_Serial, Doc_Subject, Doc_Description, Doc_date, Doc_ReceiveDate, Doc_to, DocToName, Org_Name, Doc_Priority_ID, Doc_Priority_Name, Doc_Secret_ID, Doc_Status_ID, Doc_Status_Name,empEmail,endDate,startDate,taskDetail,IsDueDate,DocPriorityID,chkAssign,DocBookBeforeSign_ID,DocBookBeforeSign_Name,DocAttachment_ID,DocAttachment_Name];
            
            
            const char * consCharInsertStatement = [strInsertStatement UTF8String];
            // sqlite3_prepare_v2(DB, consCharInsertStatement, -1, &STMT, NULL);
            if(sqlite3_prepare_v2(DB, consCharInsertStatement, -1, &STMT, NULL) != SQLITE_OK)
            {
                NSAssert1(0, @"Error statement. '%s'", sqlite3_errmsg(DB));
            }
            
            if (sqlite3_step(STMT) == SQLITE_DONE)
            {
                //  NSLog(@"OK");
                
            } else {
                // NSLog(@"sqlite3_step error: '%s'", sqlite3_errmsg(DB));
                //  NSLog(@"Problem!!!");
                
            }
            
            sqlite3_step(STMT);
        }
    }
    
    [self closeDB];
}
//-(EGAarcAssignCell*) saveBook
//{
//    //NSMutableArray * returnArr = [[NSMutableArray alloc] init];
//    EGAarcAssignCell *item = [[EGAarcAssignCell alloc]init];
//    [self initialDB];
//    
//    //check internet access
//    if([self internetConnected])
//    {
//        NSString *ID;
//        const char * queryStatement = [@"select Min(ID) from ListBook " UTF8String];
//        sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
//        
//        //fetch each to dict and add to arr
//        while ((sqlite3_step(STMT)) == SQLITE_ROW) {
//            ID= [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 0)];
//            //NSLog(@"Min ID  xx:%@", ID );
//            
//        }
//        //at first delete old data
//        //        NSString * strDeleteStatement = [NSString stringWithFormat:@"DELETE FROM ListBook"];
//        //        const char * consCharDeleteStatement = [strDeleteStatement UTF8String];
//        //        sqlite3_prepare_v2(DB, consCharDeleteStatement, -1, &STMT, NULL);
//        //        sqlite3_step(STMT);
//        
//        //get json into array
//        NSMutableArray * arrOfDict = [[NSMutableArray alloc] init];
//        
//        
//      
//        
//        NSString *jsonURL=[NSString stringWithFormat:@"http://%@ListBook.ashx?",serviceUrl];
//        NSMutableURLRequest *request = [[NSMutableURLRequest alloc] initWithURL: [NSURL URLWithString: jsonURL]];
//        
//        
//        
//        NSString *post = [NSString stringWithFormat:@"startID=%@",ID];
//        NSData *postData = [post dataUsingEncoding:NSASCIIStringEncoding allowLossyConversion:YES];
//        NSString *postLength = [NSString stringWithFormat:@"%d", [postData length]];
//        
//        [request setHTTPMethod:@"POST"];
//        [request setValue:postLength forHTTPHeaderField:@"Content-Length"];
//        [request setValue:@"application/x-www-form-urlencoded" forHTTPHeaderField:@"Content-Type"];
//        [request setHTTPBody:postData];
//        
//        NSData *json = [NSURLConnection sendSynchronousRequest: request returningResponse: nil error: nil ];
//        // NSData *json = [NSData dataWithContentsOfURL:[NSURL URLWithString:jsonURL]];
//        
//        
//        arrOfDict = [NSJSONSerialization JSONObjectWithData:json options:kNilOptions error:nil];
//        //insert json arr to DB
//        sqlite3_step(STMT);
//        NSInteger row=0;
//        for(NSDictionary *dictForEachRow in arrOfDict)
//        {
//            
//            
//            row=row+1;
//            NSString * ID = [dictForEachRow objectForKey:@"ID"];
//            NSLog(@"  ID  xx:%@", ID );
//            NSString * Doc_FiscalYear = [dictForEachRow objectForKey:@"Doc_FiscalYear"];
//            NSString * Doc_Register = [dictForEachRow objectForKey:@"Doc_Register"];
//            NSString * Doc_Serial = [dictForEachRow objectForKey:@"Doc_Serial"];
//            NSString * Doc_Subject = [dictForEachRow objectForKey:@"Doc_Subject"];
//            NSString * Doc_Description = [dictForEachRow objectForKey:@"Doc_Description"];
//            NSString * Doc_date = [dictForEachRow objectForKey:@"Doc_date"];
//            NSString * Doc_ReceiveDate = [dictForEachRow objectForKey:@"Doc_ReceiveDate"];
//            NSString * Doc_to = [dictForEachRow objectForKey:@"Doc_to"];
//            NSString * DocToName = [dictForEachRow objectForKey:@"DocToName"];
//            NSString * Org_Name = [dictForEachRow objectForKey:@"Org_Name"];
//            NSString * Doc_Priority_ID = [dictForEachRow objectForKey:@"Doc_Priority_ID"];
//            NSString * Doc_Priority_Name = [dictForEachRow objectForKey:@"Doc_Priority_Name"];
//            NSString * Doc_Secret_ID = [dictForEachRow objectForKey:@"Doc_Secret_ID"];
//            NSString * Doc_Status_ID = [dictForEachRow objectForKey:@"Doc_Status_ID"];
//            NSString * Doc_Status_Name = [dictForEachRow objectForKey:@"Doc_Status_Name"];
//            
//            NSString * empEmail = [dictForEachRow objectForKey:@"empEmail"];
//            NSString * endDate = [dictForEachRow objectForKey:@"endDate"];
//            NSString * startDate = [dictForEachRow objectForKey:@"startDate"];
//            NSString * taskDetail = [dictForEachRow objectForKey:@"taskDetail"];
//            NSString * IsDueDate = [dictForEachRow objectForKey:@"IsDueDate"];
//            NSString * DocPriorityID = [dictForEachRow objectForKey:@"DocPriorityID"];
//            NSString * chkAssign = [dictForEachRow objectForKey:@"chkAssign"];
//            
//            NSString * DocBookBeforeSign_ID = [dictForEachRow objectForKey:@"DocBookBeforeSign_ID"];
//            NSString * DocBookBeforeSign_Name = [dictForEachRow objectForKey:@"DocBookBeforeSign_Name"];
//            NSString * DocAttachment_ID = [dictForEachRow objectForKey:@"DocAttachment_ID"];
//            NSString * DocAttachment_Name = [dictForEachRow objectForKey:@"DocAttachment_Name"];
//            
//            //
//            NSString * strInsertStatement = [NSString stringWithFormat:@"INSERT INTO ListBook (ID, Doc_FiscalYear, Doc_Register, Doc_Serial, Doc_Subject, Doc_Description, Doc_date, Doc_ReceiveDate, Doc_to, DocToName, Org_Name, Doc_Priority_ID, Doc_Priority_Name, Doc_Secret_ID, Doc_Status_ID, Doc_Status_Name,empEmail,endDate,startDate,taskDetail,IsDueDate,DocPriorityID,chkAssign,DocBookBeforeSign_ID,DocBookBeforeSign_Name,DocAttachment_ID,DocAttachment_Name) VALUES ('%@', '%@','%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@')", ID, Doc_FiscalYear, Doc_Register, Doc_Serial, Doc_Subject, Doc_Description, Doc_date, Doc_ReceiveDate, Doc_to, DocToName, Org_Name, Doc_Priority_ID, Doc_Priority_Name, Doc_Secret_ID, Doc_Status_ID, Doc_Status_Name,empEmail,endDate,startDate,taskDetail,IsDueDate,DocPriorityID,chkAssign,DocBookBeforeSign_ID,DocBookBeforeSign_Name,DocAttachment_ID,DocAttachment_Name];
//            
//            
//            item.ID = [ID integerValue];
//            item.Doc_FiscalYear =[Doc_FiscalYear  integerValue];
//            item.Doc_Register = Doc_Register ;
//            item.Doc_Serial =  Doc_Serial ;
//            item.Doc_Subject =  Doc_Subject;
//            item.Doc_Description = Doc_Description;
//            item.Doc_date = Doc_date;
//            item.Doc_ReciveDate =Doc_ReceiveDate;
//            item.Doc_to =Doc_to;
//            item.DocToName = DocToName ;
//            
//            item.Org_Name = Org_Name;
//            item.Doc_Priority_ID =[Doc_Priority_ID  integerValue];
//            item.Doc_priority_Name =Doc_Priority_Name ;
//            item.Doc_Secret_ID =[Doc_Secret_ID  integerValue];
//            item.Doc_Status_ID = Doc_Status_ID;
//            item.Doc_Status_Name =Doc_Status_Name ;
//            item.chkAssign =[chkAssign  integerValue];;
//            item.empEmail = empEmail ;
//            item.taskDetail = taskDetail ;
//            item.DocPriorityID =DocPriorityID;
//            item.DocPriorityID =DocPriorityID ;
//            item.startDate = startDate;
//            item.endDate =endDate;
//            item.IsDueDate = IsDueDate ;
//            
//            item.DocBookBeforeSign_ID = DocBookBeforeSign_ID ;
//            item.DocBookBeforeSign_Name = IsDueDate ;
//            item.DocAttachment_ID = DocAttachment_ID ;
//            item.DocAttachment_Name = DocAttachment_Name ;
//
//            
//            //           [returnArr addObject:item];
//            const char * consCharInsertStatement = [strInsertStatement UTF8String];
//            // sqlite3_prepare_v2(DB, consCharInsertStatement, -1, &STMT, NULL);
//            if(sqlite3_prepare_v2(DB, consCharInsertStatement, -1, &STMT, NULL) != SQLITE_OK)
//            {
//                NSAssert1(0, @"Error statement. '%s'", sqlite3_errmsg(DB));
//            }
//            
//            if (sqlite3_step(STMT) == SQLITE_DONE)
//            {
//                NSLog(@"OK");
//                
//            } else {
//                NSLog(@"sqlite3_step error: '%s'", sqlite3_errmsg(DB));
//                NSLog(@"Problem!!!");
//                
//            }
//            
//            sqlite3_step(STMT);
//        }
//    }
//    
//    [self closeDB];
//    return item;
//}
-(NSMutableArray*) getBook
{
    [self initialDB];
    
    NSMutableArray * returnArr = [[NSMutableArray alloc] init];
    
    
    //prepare statement
    const char * queryStatement = [@"select * from ListBook order by ID desc" UTF8String];
    sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
    
    //fetch each to dict and add to arr
    while ((sqlite3_step(STMT)) == SQLITE_ROW) {
        NSString *ID                = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 0)];
        NSString *Doc_FiscalYear    = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 1)];
        NSString *Doc_Register      = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 2)];
        NSString *Doc_Serial        = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 3)];
        NSString *Doc_Subject       = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 4)];
        NSString *Doc_Description   = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 5)];
        NSString *Doc_date          = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 6)];
        NSString *Doc_ReceiveDate   = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 7)];
        NSString *Doc_to            = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 8)];
        NSString *DocToName         = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 9)];
        NSString *Org_Name          = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 10)];
        NSString *Doc_Priority_ID   = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 11)];
        NSString *Doc_Priority_Name = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 12)];
        NSString *Doc_Secret_ID     = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 13)];
        NSString *Doc_Status_ID     = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 14)];
        NSString *Doc_Status_Name   = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 15)];
        
        
        NSString * empEmail = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 16)];
        NSString * endDate =  [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 17)];
        NSString * startDate =  [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 18)];
        NSString * taskDetail =  [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 19)];
        //  NSString * IsDueDate =  [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 20)];
        //  NSString * DocPriorityID = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 21)];
        //  NSString * chkAssign =  [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 22)];
        
        
        NSString * DocBookBeforeSign_ID =  [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 23)];
        NSString * DocBookBeforeSign_Name =  [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 24)];
        NSString * DocAttachment_ID =  [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 25)];
        NSString * DocAttachment_Name =  [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 26)];

        

        
        NSDictionary * dicForEachRow = [[NSDictionary alloc] initWithObjectsAndKeys:
                                        ID, @"ID",
                                        Doc_FiscalYear, @"Doc_FiscalYear",
                                        Doc_Register, @"Doc_Register",
                                        Doc_Serial, @"Doc_Serial",
                                        Doc_Subject, @"Doc_Subject",
                                        Doc_Description, @"Doc_Description",
                                        Doc_date, @"Doc_date",
                                        Doc_ReceiveDate, @"Doc_ReceiveDate",
                                        Doc_to, @"Doc_to",
                                        DocToName, @"DocToName",
                                        Org_Name, @"Org_Name",
                                        Doc_Priority_ID, @"Doc_Priority_ID",
                                        Doc_Priority_Name, @"Doc_Priority_Name",
                                        Doc_Secret_ID, @"Doc_Secret_ID",
                                        Doc_Status_ID, @"Doc_Status_ID",
                                        Doc_Status_Name, @"Doc_Status_Name",
                                        
                                        empEmail , @"empEmail",
                                        endDate ,   @"endDate",
                                        startDate ,   @"startDate",
                                        taskDetail ,  @"taskDetail",
                                        //                                         IsDueDate =   @"IsDueDate",
                                        //                                         DocPriorityID =  @"DocPriorityID",
                                        //                                         chkAssign =  @"chkAssign",
                                        
                                        DocBookBeforeSign_ID , @"DocBookBeforeSign_ID",
                                        DocBookBeforeSign_Name ,   @"DocBookBeforeSign_Name",
                                        DocAttachment_ID ,   @"DocAttachment_ID",
                                        DocAttachment_Name ,  @"DocAttachment_Name",
                                        
                                        
                                        nil];
        [returnArr addObject:dicForEachRow];
    }
    
    [self closeDB];
    
    return returnArr;
}
-(NSMutableArray*) getBooktest
{
    [self initialDB];
    
   
    
   // NSError *error;
    NSMutableArray *result = [[NSMutableArray alloc]init];
    
    //prepare statement
    const char * queryStatement = [@"select * from ListBook order by chkAssign desc , ID desc" UTF8String];
    sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
    
    //fetch each to dict and add to arr
    while ((sqlite3_step(STMT)) == SQLITE_ROW) {
        
        NSString *ID                = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 0)];
        NSString *Doc_FiscalYear    = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 1)];
        NSString *Doc_Register      = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 2)];
        NSString *Doc_Serial        = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 3)];
        NSString *Doc_Subject       = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 4)];
        NSString *Doc_Description   = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 5)];
        NSString *Doc_date          = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 6)];
        NSString *Doc_ReceiveDate   = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 7)];
        NSString *Doc_to            = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 8)];
        NSString *DocToName         = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 9)];
        NSString *Org_Name          = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 10)];
        NSString *Doc_Priority_ID   = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 11)];
        NSString *Doc_Priority_Name = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 12)];
        NSString *Doc_Secret_ID     = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 13)];
        NSString *Doc_Status_ID     = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 14)];
        NSString *Doc_Status_Name   = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 15)];
        
        
        NSString * empEmail         = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 16)];
        NSString * endDate          =  [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 17)];
        NSString * startDate        =  [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 18)];
        NSString * taskDetail       =  [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 19)];
        NSString * IsDueDate        =  [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 20)];
        NSString * DocPriorityID    = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 21)];
        NSString * chkAssign        =  [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 22)];
        
        NSString * DocBookBeforeSign_ID        =  [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 23)];
        NSString * DocBookBeforeSign_Name        =  [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 24)];
        NSString * DocAttachment_ID        =  [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 25)];
        NSString * DocAttachment_Name        =  [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 26)];

        
        
        
        EGAarcAssignCell *item = [[EGAarcAssignCell alloc]init];
        item.ID = [ID integerValue];
        item.Doc_FiscalYear =[Doc_FiscalYear  integerValue];
        item.Doc_Register = Doc_Register ;
        item.Doc_Serial =  Doc_Serial ;
        item.Doc_Subject =  Doc_Subject;
        item.Doc_Description = Doc_Description;
        item.Doc_date = Doc_date;
        item.Doc_ReciveDate =Doc_ReceiveDate;
        item.Doc_to =Doc_to;
        item.DocToName = DocToName ;
       
        item.Org_Name = Org_Name;
        item.Doc_Priority_ID =[Doc_Priority_ID  integerValue];
        item.Doc_priority_Name =Doc_Priority_Name ;
        item.Doc_Secret_ID =[Doc_Secret_ID  integerValue];
        item.Doc_Status_ID = Doc_Status_ID;
        item.Doc_Status_Name =Doc_Status_Name ;
        item.chkAssign =[chkAssign  integerValue];;
        item.empEmail = empEmail ;
        item.taskDetail = taskDetail ;
        item.DocPriorityID =DocPriorityID;
        item.DocPriorityID =DocPriorityID ;
        item.startDate = startDate;
        item.endDate =endDate;
        item.IsDueDate = IsDueDate ;
        
        item.DocBookBeforeSign_ID = DocBookBeforeSign_ID ;
        item.DocBookBeforeSign_Name = DocBookBeforeSign_Name ;
        item.DocAttachment_ID = DocAttachment_ID ;
        item.DocAttachment_Name = DocAttachment_Name ;

        

        
       // NSLog(@"empEmail%@",empEmail);
        [result addObject:item];
        NSInteger i = 0;
        NSLog(@"count%d",i+1);

                                        
      
    }
    
    [self closeDB];
    
    return result;
}

-(NSDictionary*) getOneBookDetail:(NSString*)BookID
{
    [self initialDB];
    
    NSDictionary * returnDict = [[NSDictionary alloc] init];
    
    NSString* SQL = [NSString stringWithFormat:@"SELECT * FROM ListBook WHERE ID = '%@'",BookID];
    
    //prepare statement
    const char * queryStatement = [SQL UTF8String];
    sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
    
    //fetch each to dict and add to arr
    while ((sqlite3_step(STMT)) == SQLITE_ROW) {
        NSString *ID                = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 0)];
        NSString *Doc_FiscalYear    = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 1)];
        NSString *Doc_Register      = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 2)];
        NSString *Doc_Serial        = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 3)];
        NSString *Doc_Subject       = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 4)];
        NSString *Doc_Description   = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 5)];
        NSString *Doc_date          = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 6)];
        NSString *Doc_ReceiveDate   = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 7)];
        NSString *Doc_to            = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 8)];
        NSString *DocToName         = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 9)];
        NSString *Org_Name          = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 10)];
        NSString *Doc_Priority_ID   = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 11)];
        NSString *Doc_Priority_Name = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 12)];
        NSString *Doc_Secret_ID     = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 13)];
        NSString *Doc_Status_ID     = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 14)];
        NSString *Doc_Status_Name   = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 15)];
        
        NSString * DocBookBeforeSign_ID        =  [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 23)];
        NSString * DocBookBeforeSign_Name        =  [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 24)];
        NSString * DocAttachment_ID        =  [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 25)];
        NSString * DocAttachment_Name        =  [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 26)];
        
        returnDict = [[NSDictionary alloc] initWithObjectsAndKeys:
                                        ID, @"ID",
                                        Doc_FiscalYear, @"Doc_FiscalYear",
                                        Doc_Register, @"Doc_Register",
                                        Doc_Serial, @"Doc_Serial",
                                        Doc_Subject, @"Doc_Subject",
                                        Doc_Description, @"Doc_Description",
                                        Doc_date, @"Doc_date",
                                        Doc_ReceiveDate, @"Doc_ReceiveDate",
                                        Doc_to, @"Doc_to",
                                        DocToName, @"DocToName",
                                        Org_Name, @"Org_Name",
                                        Doc_Priority_ID, @"Doc_Priority_ID",
                                        Doc_Priority_Name, @"Doc_Priority_Name",
                                        Doc_Secret_ID, @"Doc_Secret_ID",
                                        Doc_Status_ID, @"Doc_Status_ID",
                                        Doc_Status_Name, @"Doc_Status_Name",
                      
                                        DocBookBeforeSign_ID, @"DocBookBeforeSign_ID",
                                        DocBookBeforeSign_Name, @"DocBookBeforeSign_Name",
                                        DocAttachment_ID, @"DocAttachment_ID",
                                        DocAttachment_Name, @"DocAttachment_Name",

                                        nil];
    }
    
    [self closeDB];
    
    
    return returnDict;
}
-(void) updateOneBookDetail:(NSString*)BookID
{
    [self saveAllBook];
}




















#pragma alltask
-(BOOL)isHasAllTaskInDB
{
    [self initialDB];
    
    //prepare statement
    const char * queryStatement = [@"SELECT * FROM ListAllTask" UTF8String];
    sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
    
    //fetch each to dict and add to arr
    int RowNum = 0;
    while ((sqlite3_step(STMT)) == SQLITE_ROW) {
        RowNum += 1;
    }
    
    [self closeDB];
    
    if (RowNum != 0)
    {
        return YES;
    }
    else
    {
        return NO;
    }
}
-(void) saveAllTask
{
    [self initialDB];
    
    //check internet access
    if([self internetConnected])
    {
        //at first delete old data
        NSString * strDeleteStatement = [NSString stringWithFormat:@"DELETE FROM ListAllTask"];
        const char * consCharDeleteStatement = [strDeleteStatement UTF8String];
        sqlite3_prepare_v2(DB, consCharDeleteStatement, -1, &STMT, NULL);
        sqlite3_step(STMT);
        
        

    
        //get json into array
        NSMutableArray * arrOfDict = [[NSMutableArray alloc] init];
//        NSString *jsonURL=[NSString stringWithFormat:@"http://%@ListAllTask.ashx?typeID=0",serviceUrl];
//        NSData *json = [NSData dataWithContentsOfURL:[NSURL URLWithString:jsonURL]];

        /////////////////new
        EGAUtility * Util = [[EGAUtility alloc]init];
        NSString *strURL=@"ws=ListAllTask.ashx&typeID=0";
        NSData *json =[Util getDataFromService:strURL];
        /////////////////new
        

        arrOfDict = [NSJSONSerialization JSONObjectWithData:json options:kNilOptions error:nil];
        //insert json ListAllTask to DB
        sqlite3_step(STMT);
        for(NSDictionary *dictForEachRow in arrOfDict)
        {
            NSString * Type = [dictForEachRow objectForKey:@"Type"];
            NSString * ID = [dictForEachRow objectForKey:@"ID"];
            NSString * Doc_Register = [dictForEachRow objectForKey:@"Doc_Register"];
            NSString * Doc_Subject = [dictForEachRow objectForKey:@"Doc_Subject"];
            NSString * Doc_FiscalYear = [dictForEachRow objectForKey:@"Doc_FiscalYear"];
            NSString * TaskStatus = [dictForEachRow objectForKey:@"TaskStatus"];
            NSString * CommandDate = [dictForEachRow objectForKey:@"CommandDate"];

            
            NSString * strInsertStatement = [NSString stringWithFormat:@"INSERT INTO ListAllTask (Type,ID,Doc_Register,Doc_Subject,Doc_FiscalYear,TaskStatus,CommandDate) VALUES ('%@', '%@','%@', '%@', '%@', '%@', '%@')",Type,ID,Doc_Register,Doc_Subject,Doc_FiscalYear,TaskStatus,CommandDate];
            
            const char * consCharInsertStatement = [strInsertStatement UTF8String];
            sqlite3_prepare_v2(DB, consCharInsertStatement, -1, &STMT, NULL);
            sqlite3_step(STMT);
        }
    }
    
    [self closeDB];
}
-(NSMutableArray*) getAllTaskType0
{
    [self initialDB];
    
    NSMutableArray * returnArr = [[NSMutableArray alloc] init];
    
    //prepare statement
    const char * queryStatement = [@"select * from ListAllTask" UTF8String];
    sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
    
    //fetch each to dict and add to arr
    while ((sqlite3_step(STMT)) == SQLITE_ROW) {
        NSString *Type              = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 0)];
        NSString *ID                = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 1)];
        NSString *Doc_Register      = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 2)];
        NSString *Doc_Subject       = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 3)];
        NSString *Doc_FiscalYear    = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 4)];
        NSString *TaskStatus        = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 5)];
        NSString *CommandDate       = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 6)];

        
        NSDictionary * dicForEachRow = [[NSDictionary alloc] initWithObjectsAndKeys:
                                        Type, @"Type",
                                        ID, @"ID",
                                        Doc_Register, @"Doc_Register",
                                        Doc_Subject, @"Doc_Subject",
                                        Doc_FiscalYear, @"Doc_FiscalYear",
                                        TaskStatus, @"TaskStatus",
                                        CommandDate, @"CommandDate",
                                        nil];
        [returnArr addObject:dicForEachRow];
    }
    
    [self closeDB];
    
    return returnArr;
}
-(NSMutableArray*) getAllTaskType1
{
    [self initialDB];
    
    NSMutableArray * returnArr = [[NSMutableArray alloc] init];
    
    //prepare statement
    const char * queryStatement = [@"select * from ListAllTask where Type = '1'" UTF8String];
    sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
    
    //fetch each to dict and add to arr
    while ((sqlite3_step(STMT)) == SQLITE_ROW) {
        NSString *Type              = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 0)];
        NSString *ID                = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 1)];
        NSString *Doc_Register      = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 2)];
        NSString *Doc_Subject       = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 3)];
        NSString *Doc_FiscalYear    = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 4)];
        NSString *TaskStatus        = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 5)];
        NSString *CommandDate       = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 6)];
        
        
        NSDictionary * dicForEachRow = [[NSDictionary alloc] initWithObjectsAndKeys:
                                        Type, @"Type",
                                        ID, @"ID",
                                        Doc_Register, @"Doc_Register",
                                        Doc_Subject, @"Doc_Subject",
                                        Doc_FiscalYear, @"Doc_FiscalYear",
                                        TaskStatus, @"TaskStatus",
                                        CommandDate, @"CommandDate",
                                        nil];
        [returnArr addObject:dicForEachRow];
    }
    
    [self closeDB];
    
    return returnArr;
}
-(NSMutableArray*) getAllTaskType2
{
    [self initialDB];
    
    NSMutableArray * returnArr = [[NSMutableArray alloc] init];
    
    //prepare statement
    const char * queryStatement = [@"select * from ListAllTask where Type = '2'" UTF8String];
    sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
    
    //fetch each to dict and add to arr
    while ((sqlite3_step(STMT)) == SQLITE_ROW) {
        NSString *Type              = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 0)];
        NSString *ID                = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 1)];
        NSString *Doc_Register      = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 2)];
        NSString *Doc_Subject       = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 3)];
        NSString *Doc_FiscalYear    = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 4)];
        NSString *TaskStatus        = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 5)];
        NSString *CommandDate       = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 6)];

        
        
        NSDictionary * dicForEachRow = [[NSDictionary alloc] initWithObjectsAndKeys:
                                        Type, @"Type",
                                        ID, @"ID",
                                        Doc_Register, @"Doc_Register",
                                        Doc_Subject, @"Doc_Subject",
                                        Doc_FiscalYear, @"Doc_FiscalYear",
                                        TaskStatus, @"TaskStatus",
                                        CommandDate, @"CommandDate",
                                        nil];
        [returnArr addObject:dicForEachRow];
    }
    
    [self closeDB];
    
    return returnArr;
}
-(NSMutableArray*) getAllTaskType3
{
    [self initialDB];
    
    NSMutableArray * returnArr = [[NSMutableArray alloc] init];
    
    //prepare statement
    const char * queryStatement = [@"select * from ListAllTask where Type = '3'" UTF8String];
    sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
    
    //fetch each to dict and add to arr
    while ((sqlite3_step(STMT)) == SQLITE_ROW) {
        NSString *Type              = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 0)];
        NSString *ID                = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 1)];
        NSString *Doc_Register      = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 2)];
        NSString *Doc_Subject       = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 3)];
        NSString *Doc_FiscalYear    = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 4)];
        NSString *TaskStatus        = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 5)];
        NSString *CommandDate       = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 6)];

        
        
        NSDictionary * dicForEachRow = [[NSDictionary alloc] initWithObjectsAndKeys:
                                        Type, @"Type",
                                        ID, @"ID",
                                        Doc_Register, @"Doc_Register",
                                        Doc_Subject, @"Doc_Subject",
                                        Doc_FiscalYear, @"Doc_FiscalYear",
                                        TaskStatus, @"TaskStatus",
                                        CommandDate, @"CommandDate",
                                        nil];
        [returnArr addObject:dicForEachRow];
    }
    
    [self closeDB];
    
    return returnArr;
}
-(NSDictionary*) countAllTask
{
    NSDictionary*dictCountAllTask;
    [self initialDB];
    
    //prepare statement
    const char * queryStatement = [@"SELECT      (SELECT COUNT(*) FROM ListAllTask) as CountAllTask   ,   (SELECT COUNT(*) FROM ListAllTask WHERE Type = 1) as CountNotProgressAllTask ,   (SELECT COUNT(*) FROM ListAllTask WHERE Type = 2) as CountInProgressAllTask  ,   (SELECT COUNT(*) FROM ListAllTask WHERE Type = 3) as CountFinishedAllTask" UTF8String];
    sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
    
    //fetch each to dict and add to arr
    while ((sqlite3_step(STMT)) == SQLITE_ROW) {
        NSString *CountAllTask              = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 0)];
        NSString *CountNotProgressAllTask   = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 1)];
        NSString *CountInProgressAllTask    = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 2)];
        NSString *CountFinishedAllTask      = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 3)];
        
        dictCountAllTask = [[NSDictionary alloc] initWithObjectsAndKeys:
                            CountAllTask, @"CountAllTask",
                            CountNotProgressAllTask, @"CountNotProgressAllTask",
                            CountInProgressAllTask, @"CountInProgressAllTask",
                            CountFinishedAllTask, @"CountFinishedAllTask",
                            nil];
    }
    [self closeDB];
    return dictCountAllTask;
}







#pragma mytask
-(BOOL)isHasMyTaskInDB
{
    [self initialDB];
    
    //prepare statement
    const char * queryStatement = [@"SELECT * FROM ListMyTask" UTF8String];
    sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
    
    //fetch each to dict and add to arr
    int RowNum = 0;
    while ((sqlite3_step(STMT)) == SQLITE_ROW) {
        RowNum += 1;
    }
    
    [self closeDB];
    
    if (RowNum != 0)
    {
        return YES;
    }
    else
    {
        return NO;
    }
}
-(void) saveMyTask:(NSString*)EmpID
{
    [self initialDB];
    
    //check internet access
    if([self internetConnected])
    {
        //at first delete old data
        NSString * strDeleteStatement = [NSString stringWithFormat:@"DELETE FROM ListMyTask"];
        const char * consCharDeleteStatement = [strDeleteStatement UTF8String];
        sqlite3_prepare_v2(DB, consCharDeleteStatement, -1, &STMT, NULL);
        sqlite3_step(STMT);
        
        //get json into array
        NSMutableArray * arrOfDict = [[NSMutableArray alloc] init];

//        NSString *jsonURL=[NSString stringWithFormat:@"http://%@ListMyTask.ashx?typeID=0&EmpID=",serviceUrl];
//        NSString * strURL = [jsonURL stringByAppendingString:EmpID];
//        NSData *json = [NSData dataWithContentsOfURL:[NSURL URLWithString:strURL]];
        
        /////////////////new
        EGAUtility * Util = [[EGAUtility alloc]init];
        NSString *strURL=[NSString stringWithFormat:@"ws=ListMyTask.ashx&typeID=0&EmpID=%@",EmpID];
        NSData *json =[Util getDataFromService:strURL];
        /////////////////new
        
        arrOfDict = [NSJSONSerialization JSONObjectWithData:json options:kNilOptions error:nil];
        
        //insert json ListAllTask to DB
        sqlite3_step(STMT);
        for(NSDictionary *dictForEachRow in arrOfDict)
        {
            NSString * Type = [dictForEachRow objectForKey:@"Type"];
            NSString * ID = [dictForEachRow objectForKey:@"ID"];
            NSString * Doc_Register = [dictForEachRow objectForKey:@"Doc_Register"];
            NSString * Doc_Subject = [dictForEachRow objectForKey:@"Doc_Subject"];
            NSString * Doc_FiscalYear = [dictForEachRow objectForKey:@"Doc_FiscalYear"];
            NSString * TaskStatus = [dictForEachRow objectForKey:@"TaskStatus"];
            NSString * CommandDate = [dictForEachRow objectForKey:@"CommandDate"];

            
            NSString * strInsertStatement = [NSString stringWithFormat:@"INSERT INTO ListMyTask (Type,ID,Doc_Register,Doc_Subject,Doc_FiscalYear,TaskStatus,CommandDate) VALUES ('%@', '%@','%@', '%@', '%@', '%@', '%@')",Type,ID,Doc_Register,Doc_Subject,Doc_FiscalYear,TaskStatus,CommandDate];
            
            const char * consCharInsertStatement = [strInsertStatement UTF8String];
            sqlite3_prepare_v2(DB, consCharInsertStatement, -1, &STMT, NULL);
            
            sqlite3_step(STMT);
        }
    }
    
    [self closeDB];
}
-(NSMutableArray*) getMyTaskType0
{
    [self initialDB];
    
    NSMutableArray * returnArr = [[NSMutableArray alloc] init];
    
    //prepare statement
    const char * queryStatement = [@"select * from ListMyTask" UTF8String];
    sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
    
    //fetch each to dict and add to arr
    while ((sqlite3_step(STMT)) == SQLITE_ROW) {
        NSString *Type              = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 0)];
        NSString *ID                = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 1)];
        NSString *Doc_Register      = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 2)];
        NSString *Doc_Subject       = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 3)];
        NSString *Doc_FiscalYear    = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 4)];
        NSString *TaskStatus        = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 5)];
        NSString *CommandDate       = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 6)];

        
        
        NSDictionary * dicForEachRow = [[NSDictionary alloc] initWithObjectsAndKeys:
                                        Type, @"Type",
                                        ID, @"ID",
                                        Doc_Register, @"Doc_Register",
                                        Doc_Subject, @"Doc_Subject",
                                        Doc_FiscalYear, @"Doc_FiscalYear",
                                        TaskStatus, @"TaskStatus",
                                        CommandDate, @"CommandDate",
                                        nil];
        [returnArr addObject:dicForEachRow];
    }
    
    [self closeDB];
    
    return returnArr;
}
-(NSMutableArray*) getMyTaskType1
{
    [self initialDB];
    
    NSMutableArray * returnArr = [[NSMutableArray alloc] init];
    
    //prepare statement
    const char * queryStatement = [@"select * from ListMyTask where Type = '1'" UTF8String];
    sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
    
    //fetch each to dict and add to arr
    while ((sqlite3_step(STMT)) == SQLITE_ROW) {
        NSString *Type              = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 0)];
        NSString *ID                = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 1)];
        NSString *Doc_Register      = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 2)];
        NSString *Doc_Subject       = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 3)];
        NSString *Doc_FiscalYear    = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 4)];
        NSString *TaskStatus        = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 5)];
        NSString *CommandDate       = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 6)];

        
        
        NSDictionary * dicForEachRow = [[NSDictionary alloc] initWithObjectsAndKeys:
                                        Type, @"Type",
                                        ID, @"ID",
                                        Doc_Register, @"Doc_Register",
                                        Doc_Subject, @"Doc_Subject",
                                        Doc_FiscalYear, @"Doc_FiscalYear",
                                        TaskStatus, @"TaskStatus",
                                        CommandDate, @"CommandDate",
                                        nil];
        [returnArr addObject:dicForEachRow];
    }
    
    [self closeDB];
    
    return returnArr;
}
-(NSMutableArray*) getMyTaskType2
{
    [self initialDB];
    
    NSMutableArray * returnArr = [[NSMutableArray alloc] init];
    
    //prepare statement
    const char * queryStatement = [@"select * from ListMyTask where Type = '2'" UTF8String];
    sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
    
    //fetch each to dict and add to arr
    while ((sqlite3_step(STMT)) == SQLITE_ROW) {
        NSString *Type              = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 0)];
        NSString *ID                = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 1)];
        NSString *Doc_Register      = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 2)];
        NSString *Doc_Subject       = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 3)];
        NSString *Doc_FiscalYear    = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 4)];
        NSString *TaskStatus        = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 5)];
        NSString *CommandDate       = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 6)];

        
        
        NSDictionary * dicForEachRow = [[NSDictionary alloc] initWithObjectsAndKeys:
                                        Type, @"Type",
                                        ID, @"ID",
                                        Doc_Register, @"Doc_Register",
                                        Doc_Subject, @"Doc_Subject",
                                        Doc_FiscalYear, @"Doc_FiscalYear",
                                        TaskStatus, @"TaskStatus",
                                        CommandDate, @"CommandDate",
                                        nil];
        [returnArr addObject:dicForEachRow];
    }
    
    [self closeDB];
    
    return returnArr;
}
-(NSMutableArray*) getMyTaskType3
{
    [self initialDB];
    
    NSMutableArray * returnArr = [[NSMutableArray alloc] init];
    
    //prepare statement
    const char * queryStatement = [@"select * from ListMyTask where Type = '3'" UTF8String];
    sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
    
    //fetch each to dict and add to arr
    while ((sqlite3_step(STMT)) == SQLITE_ROW) {
        NSString *Type              = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 0)];
        NSString *ID                = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 1)];
        NSString *Doc_Register      = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 2)];
        NSString *Doc_Subject       = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 3)];
        NSString *Doc_FiscalYear    = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 4)];
        NSString *TaskStatus        = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 5)];
        NSString *CommandDate       = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 6)];

        
        
        NSDictionary * dicForEachRow = [[NSDictionary alloc] initWithObjectsAndKeys:
                                        Type, @"Type",
                                        ID, @"ID",
                                        Doc_Register, @"Doc_Register",
                                        Doc_Subject, @"Doc_Subject",
                                        Doc_FiscalYear, @"Doc_FiscalYear",
                                        TaskStatus, @"TaskStatus",
                                        CommandDate, @"CommandDate",
                                        nil];
        [returnArr addObject:dicForEachRow];
    }
    
    [self closeDB];
    
    return returnArr;
}
-(NSDictionary*) countMyTask
{
    NSDictionary*dictCountMyTask;
    [self initialDB];
    
    const char * queryStatement = [@"SELECT      (SELECT COUNT(*) FROM ListMyTask) as CountAllMyTask   ,   (SELECT COUNT(*) FROM ListMyTask WHERE Type = 1) as CountNotProgressMyTask ,   (SELECT COUNT(*) FROM ListMyTask WHERE Type = 2) as CountInProgressMyTask  ,   (SELECT COUNT(*) FROM ListMyTask WHERE Type = 3) as CountFinishedMyTask" UTF8String];
    sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
    
    //fetch to dict
    while ((sqlite3_step(STMT)) == SQLITE_ROW) {
        NSString *CountAllMyTask            = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 0)];
        NSString *CountNotProgressMyTask    = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 1)];
        NSString *CountInProgressMyTask     = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 2)];
        NSString *CountFinishedMyTask       = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 3)];
        
        dictCountMyTask = [[NSDictionary alloc] initWithObjectsAndKeys:
                           CountAllMyTask, @"CountAllMyTask",
                           CountNotProgressMyTask, @"CountNotProgressMyTask",
                           CountInProgressMyTask, @"CountInProgressMyTask",
                           CountFinishedMyTask, @"CountFinishedMyTask",
                           nil];
    }
    [self closeDB];
    return dictCountMyTask;
}








#pragma taskDetail

-(BOOL)isHasTaskDetailInDB:(NSString*)DocID
{
    [self initialDB];
    
    //prepare statement
    NSString * sql = [NSString stringWithFormat:@"SELECT * FROM TaskDetail WHERE DocumentList_ID = '%@'",DocID];
    const char * queryStatement = [sql UTF8String];
    sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
    
    //fetch each to dict and add to arr
    int RowNum = 0;
    while ((sqlite3_step(STMT)) == SQLITE_ROW) {
        RowNum += 1;
    }
    
    [self closeDB];
    
    if (RowNum != 0)
    {
        return YES;
    }
    else
    {
        return NO;
    }
}

-(void)saveTaskDetail:(NSString*)DocID taskUserOrAdmin:(NSString*)taskUserOrAdmin empID:(NSString*)empID
{
    [self initialDB];
    
    //check internet access
    if([self internetConnected])
    {
        //at first delete old data
        NSString * strDeleteStatement = [NSString stringWithFormat:@"DELETE FROM TaskDetail WHERE DocumentList_ID = '%@'",DocID];
        const char * consCharDeleteStatement = [strDeleteStatement UTF8String];
        sqlite3_prepare_v2(DB, consCharDeleteStatement, -1, &STMT, NULL);
        sqlite3_step(STMT);
        
        //get json into array
        NSMutableArray * arrOfDict = [[NSMutableArray alloc] init];

//        
//        NSString *jsonURL=[NSString stringWithFormat:@"http://%@ListTaskDetail.ashx?taskUserOrAdmin=%@&empID=%@&DocID=",serviceUrl,taskUserOrAdmin,empID];
//        NSString * strURL = [jsonURL stringByAppendingString:DocID];
        
        /////////////////new
        EGAUtility * Util = [[EGAUtility alloc]init];
        NSString *strURL=[NSString stringWithFormat:@"ws=ListTaskDetail.ashx&taskUserOrAdmin=%@&empID=%@&DocID=%@",taskUserOrAdmin,empID,DocID];
        NSData *json =[Util getDataFromService:strURL];
        /////////////////new
        
        //NSData *json = [NSData dataWithContentsOfURL:[NSURL URLWithString:strURL]];
        arrOfDict = [NSJSONSerialization JSONObjectWithData:json options:kNilOptions error:nil];
        
        //insert json ListAllTask to DB
        sqlite3_step(STMT);
        for(NSDictionary *dictForEachRow in arrOfDict)
        {
            NSString * Doc_Register         = [dictForEachRow objectForKey:@"Doc_Register"];
            NSString * Doc_Subject          = [dictForEachRow objectForKey:@"Doc_Subject"];
            NSString * Description          = [dictForEachRow objectForKey:@"Description"];
            NSString * Velocity             = [dictForEachRow objectForKey:@"Velocity"];
            NSString * Recorder             = [dictForEachRow objectForKey:@"Recorder"];
            NSString * StartDate            = [dictForEachRow objectForKey:@"StartDate"];
            NSString * DueDate              = [dictForEachRow objectForKey:@"DueDate"];
            NSString * TaskStatus           = [dictForEachRow objectForKey:@"TaskStatus"];
            NSString * DocumentList_ID      = [dictForEachRow objectForKey:@"DocumentList_ID"];
            NSString * DocBookBeforeSign_ID     = [dictForEachRow objectForKey:@"DocBookBeforeSign_ID"];
            NSString * DocBookBeforeSign_Name   = [dictForEachRow objectForKey:@"DocBookBeforeSign_Name"];
            NSString * DocAttachment_ID      = [dictForEachRow objectForKey:@"DocAttachment_ID"];
            NSString * DocAttachment_Name    = [dictForEachRow objectForKey:@"DocAttachment_Name"];
            NSString * IsFirst              = [dictForEachRow objectForKey:@"IsFirst"];
            NSString * IsSecond             = [dictForEachRow objectForKey:@"IsSecond"];
            NSString * IsCommand            = [dictForEachRow objectForKey:@"IsCommand"];
            NSString * Doc_Date             = [dictForEachRow objectForKey:@"Doc_Date"];
            NSString * Doc_ReceiveDate      = [dictForEachRow objectForKey:@"Doc_ReceiveDate"];
            NSString * Doc_Serial         = [dictForEachRow objectForKey:@"Doc_Serial"];
            NSString * IsDueDate         = [dictForEachRow objectForKey:@"IsDueDate"];
            
            
            
            
            
            NSString * strInsertStatement = [NSString stringWithFormat:@"INSERT INTO TaskDetail (IsDueDate,Doc_Serial,Doc_Register,Doc_Subject,Description,Velocity,Recorder,StartDate,DueDate,TaskStatus,DocumentList_ID,DocBookBeforeSign_ID,DocBookBeforeSign_Name,DocAttachment_ID,DocAttachment_Name,IsFirst,IsSecond,IsCommand,Doc_Date,Doc_ReceiveDate) VALUES ('%@','%@','%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@')",IsDueDate,Doc_Serial,Doc_Register,Doc_Subject,Description,Velocity,Recorder,StartDate,DueDate,TaskStatus,DocumentList_ID,DocBookBeforeSign_ID,DocBookBeforeSign_Name,DocAttachment_ID,DocAttachment_Name,IsFirst,IsSecond,IsCommand,Doc_Date,Doc_ReceiveDate];
            
            const char * consCharInsertStatement = [strInsertStatement UTF8String];
            sqlite3_prepare_v2(DB, consCharInsertStatement, -1, &STMT, NULL);
            
            sqlite3_step(STMT);
        }
    }
    
    [self closeDB];
}

-(NSMutableArray*)getTaskDetail:(NSString*)DocID
{
    [self initialDB];
    
    NSMutableArray * returnArr = [[NSMutableArray alloc] init];
    
    //prepare statement
    NSString* SQL = [NSString stringWithFormat:@"select * from TaskDetail WHERE DocumentList_ID = '%@' ",DocID];
    const char * queryStatement = [SQL UTF8String];
    sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
    
    //fetch each to dict and add to arr
    while ((sqlite3_step(STMT)) == SQLITE_ROW) {
        
        NSString * Doc_Register         = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 0)];
        NSString * Doc_Subject          = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 1)];
        NSString * Description          = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 2)];
        NSString * Velocity             = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 3)];
        NSString * DocBookBeforeSign_ID     = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 4)];
        NSString * DocAttachment_ID      = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 5)];
        NSString * Recorder             = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 6)];
        NSString * StartDate            = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 7)];
        NSString * DueDate              = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 8)];
        NSString * TaskStatus           = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 9)];
        NSString * DocumentList_ID      = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 10)];
        NSString * DocBookBeforeSign_Name   = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 11)];
        NSString * DocAttachment_Name    = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 12)];
        NSString * IsFirst              = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 13)];
        NSString * IsSecond             = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 14)];
        NSString * IsCommand            = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 15)];
        NSString * Doc_Date             = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 16)];
        NSString * Doc_ReceiveDate      = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 17)];
        NSString * Doc_Serial         = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 18)];
        NSString * IsDueDate         = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 19)];
        
        NSDictionary * dicForEachRow = [[NSDictionary alloc] initWithObjectsAndKeys:
                                        Doc_Register, @"Doc_Register",
                                        Doc_Subject, @"Doc_Subject",
                                        Description, @"Description",
                                        Velocity, @"Velocity",
                                        Recorder, @"Recorder",
                                        StartDate, @"StartDate",
                                        DueDate, @"DueDate",
                                        TaskStatus, @"TaskStatus",
                                        DocumentList_ID, @"DocumentList_ID",
                                        DocBookBeforeSign_ID, @"DocBookBeforeSign_ID",
                                        DocBookBeforeSign_Name, @"DocBookBeforeSign_Name",
                                        DocAttachment_ID, @"DocAttachment_ID",
                                        DocAttachment_Name, @"DocAttachment_Name",
                                        IsFirst, @"IsFirst",
                                        IsSecond, @"IsSecond",
                                        IsCommand, @"IsCommand",
                                        Doc_Date, @"Doc_Date",
                                        Doc_ReceiveDate, @"Doc_ReceiveDate",
                                        Doc_Serial, @"Doc_Serial",
                                        IsDueDate,@"IsDueDate",
                                        nil];
        [returnArr addObject:dicForEachRow];
    }
    
    [self closeDB];
    
    return returnArr;
}








#pragma command
-(NSMutableArray*)whoYouWillBeAbleToCommand:(NSString*)EmpID
                                      DocID:(NSString*)DocID
{
    NSMutableArray * returnArr = [[NSMutableArray alloc] init];
    
    //check internet access
    if([self internetConnected])
    {
        //gen url

        NSMutableArray * arrOfDict = [[NSMutableArray alloc] init];
        
//        NSString * strURL = [NSString stringWithFormat:@"%@WhoYouWillBeAbleToCommand.ashx?EmpID=%@&DocID=%@",serviceUrl,EmpID,DocID];
//        //get json into array
//        NSData *json = [NSData dataWithContentsOfURL:[NSURL URLWithString:strURL]];
        
        /////////////////new
        EGAUtility * Util = [[EGAUtility alloc]init];
        NSString *strURL=[NSString stringWithFormat:@"ws=WhoYouWillBeAbleToCommand.ashx&EmpID=%@&DocID=%@",EmpID,DocID];
        NSData *json =[Util getDataFromService:strURL];
        /////////////////new

    
        arrOfDict = [NSJSONSerialization JSONObjectWithData:json options:kNilOptions error:nil];
        //set return arr
        returnArr = arrOfDict;
    }
    
    return returnArr;
}












#pragma talkfirststep
-(BOOL)isHasTalkLV1InDB:(NSString*)DocID
{
    [self initialDB];
    
    //prepare statement
    NSString * sql = [NSString stringWithFormat:@"SELECT * FROM TalkFirstStep WHERE Doc_ID = '%@'",DocID];
    const char * queryStatement = [sql UTF8String];
    sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
    
    //fetch each to dict and add to arr
    int RowNum = 0;
    while ((sqlite3_step(STMT)) == SQLITE_ROW) {
        RowNum += 1;
    }
    
    [self closeDB];
    
    if (RowNum != 0)
    {
        return YES;
    }
    else
    {
        return NO;
    }
}
-(void)saveTalkLV1:(NSString*)DocID
{
    [self initialDB];
    
    //check internet access
    if([self internetConnected])
    {
        //at first delete old data
        NSString * strDeleteStatement = [NSString stringWithFormat:@"DELETE FROM TalkFirstStep WHERE Doc_ID = '%@'",DocID];
        const char * consCharDeleteStatement = [strDeleteStatement UTF8String];
        sqlite3_prepare_v2(DB, consCharDeleteStatement, -1, &STMT, NULL);
        sqlite3_step(STMT);
        
        //get json into array
        NSMutableArray * arrOfDict = [[NSMutableArray alloc] init];

        //NSString *jsonURL=[NSString stringWithFormat:@"http://%@talkFirstStep.ashx?DocID=",serviceUrl];
        //NSString * strURL = [jsonURL stringByAppendingString:DocID];
        //NSData *json = [NSData dataWithContentsOfURL:[NSURL URLWithString:strURL]];
        
        
        /////////////////new
        EGAUtility * Util = [[EGAUtility alloc]init];
        NSString *strURL=[NSString stringWithFormat:@"ws=talkFirstStep.ashx&DocID=%@",DocID];
        NSData *json =[Util getDataFromService:strURL];
        /////////////////new
        
        
        arrOfDict = [NSJSONSerialization JSONObjectWithData:json options:kNilOptions error:nil];
        
        //insert json ListAllTask to DB
        sqlite3_step(STMT);
        for(NSDictionary *dictForEachRow in arrOfDict)
        {
            NSString * ID               = [dictForEachRow objectForKey:@"ID"];
            NSString * Doc_ID           = [dictForEachRow objectForKey:@"Doc_ID"];
            NSString * Post_By          = [dictForEachRow objectForKey:@"Post_By"];
            NSString * Talk_Text        = [dictForEachRow objectForKey:@"Talk_Text"];
            NSString * TimeStamp        = [dictForEachRow objectForKey:@"TimeStamp"];
            NSString * List_File_ID     = [dictForEachRow objectForKey:@"List_File_ID"];
            NSString * List_File_Name   = [dictForEachRow objectForKey:@"List_File_Name"];
            
            NSString * strInsertStatement = [NSString stringWithFormat:@"INSERT INTO TalkFirstStep (ID,Doc_ID,Post_By,Talk_Text,TimeStamp,List_File_ID,List_File_Name) VALUES ('%@', '%@', '%@', '%@', '%@', '%@', '%@')",ID,Doc_ID,Post_By,Talk_Text,TimeStamp,List_File_ID,List_File_Name];
            
            const char * consCharInsertStatement = [strInsertStatement UTF8String];
            sqlite3_prepare_v2(DB, consCharInsertStatement, -1, &STMT, NULL);
            
            sqlite3_step(STMT);
        }
    }
    
    [self closeDB];
}
-(NSMutableArray*)getTalkLV1:(NSString*)DocID
{
    [self initialDB];
    
    NSMutableArray * returnArr = [[NSMutableArray alloc] init];
    
    //prepare statement
    NSString* SQL = [NSString stringWithFormat:@"select * from TalkFirstStep WHERE Doc_ID = '%@'",DocID];
    const char * queryStatement = [SQL UTF8String];
    sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
    
    //fetch each to dict and add to arr
    while ((sqlite3_step(STMT)) == SQLITE_ROW) {
        
        NSString * ID         = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 0)];
        NSString * Doc_ID          = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 1)];
        NSString * Post_By          = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 2)];
        NSString * Talk_Text             = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 3)];
        NSString * TimeStamp     = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 4)];
        NSString * List_File_ID      = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 5)];
        NSString * List_File_Name      = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 6)];
        
        
        NSDictionary * dicForEachRow = [[NSDictionary alloc] initWithObjectsAndKeys:
                                        ID, @"ID",
                                        Doc_ID, @"Doc_ID",
                                        Post_By, @"Post_By",
                                        Talk_Text, @"Talk_Text",
                                        TimeStamp, @"TimeStamp",
                                        List_File_ID, @"List_File_ID",
                                        List_File_Name, @"List_File_Name",
                                        nil];
        [returnArr addObject:dicForEachRow];
    }
    
    [self closeDB];
    
    return returnArr;
}










#pragma talkSecondstep
-(BOOL)isHasTalkLV2InDB:(NSString*)DocID
{
    [self initialDB];
    
    //prepare statement
    NSString * sql = [NSString stringWithFormat:@"SELECT * FROM TalkSecondStep WHERE Doc_ID = '%@'",DocID];
    const char * queryStatement = [sql UTF8String];
    sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
    
    //fetch each to dict and add to arr
    int RowNum = 0;
    while ((sqlite3_step(STMT)) == SQLITE_ROW) {
        RowNum += 1;
    }
    
    [self closeDB];
    
    if (RowNum != 0)
    {
        return YES;
    }
    else
    {
        return NO;
    }
}
-(void)saveTalkLV2:(NSString*)DocID EmpID:(NSString*)EmpID
{
    [self initialDB];
    
    //check internet access
    if([self internetConnected])
    {
        //at first delete old data
        NSString * strDeleteStatement = [NSString stringWithFormat:@"DELETE FROM TalkSecondStep WHERE Doc_ID = '%@'",DocID];
        const char * consCharDeleteStatement = [strDeleteStatement UTF8String];
        sqlite3_prepare_v2(DB, consCharDeleteStatement, -1, &STMT, NULL);
        sqlite3_step(STMT);
        
        //get json into array
        NSMutableArray * arrOfDict = [[NSMutableArray alloc] init];

        //NSString * strURL = [NSString stringWithFormat:@"http://%@talkSecondStep.ashx?DocID=%@&EmpID=%@",serviceUrl,DocID,EmpID];
        //NSData *json = [NSData dataWithContentsOfURL:[NSURL URLWithString:strURL]];
        
        /////////////////new
        EGAUtility * Util = [[EGAUtility alloc]init];
        NSString *strURL=[NSString stringWithFormat:@"ws=talkSecondStep.ashx&DocID=%@&EmpID=%@",DocID,EmpID];
        NSData *json =[Util getDataFromService:strURL];
        /////////////////new

        
        
        arrOfDict = [NSJSONSerialization JSONObjectWithData:json options:kNilOptions error:nil];
        
        //insert json ListAllTask to DB
        sqlite3_step(STMT);
        for(NSDictionary *dictForEachRow in arrOfDict)
        {
            NSString * ID               = [dictForEachRow objectForKey:@"ID"];
            NSString * Doc_ID           = [dictForEachRow objectForKey:@"Doc_ID"];
            NSString * Post_By          = [dictForEachRow objectForKey:@"Post_By"];
            NSString * Talk_Text        = [dictForEachRow objectForKey:@"Talk_Text"];
            NSString * TimeStamp        = [dictForEachRow objectForKey:@"TimeStamp"];
            NSString * List_File_ID     = [dictForEachRow objectForKey:@"List_File_ID"];
            NSString * List_File_Name   = [dictForEachRow objectForKey:@"List_File_Name"];
            
            NSString * strInsertStatement = [NSString stringWithFormat:@"INSERT INTO TalkSecondStep (ID,Doc_ID,Post_By,Talk_Text,TimeStamp,List_File_ID,List_File_Name) VALUES ('%@', '%@', '%@', '%@', '%@', '%@', '%@')",ID,Doc_ID,Post_By,Talk_Text,TimeStamp,List_File_ID,List_File_Name];
            
            const char * consCharInsertStatement = [strInsertStatement UTF8String];
            sqlite3_prepare_v2(DB, consCharInsertStatement, -1, &STMT, NULL);
            
            sqlite3_step(STMT);
        }
    }
    
    [self closeDB];
}
-(NSMutableArray*)getTalkLV2:(NSString*)DocID
{
    [self initialDB];
    
    NSMutableArray * returnArr = [[NSMutableArray alloc] init];
    
    //prepare statement
    NSString* SQL = [NSString stringWithFormat:@"select * from TalkSecondStep WHERE Doc_ID = '%@'",DocID];
    const char * queryStatement = [SQL UTF8String];
    sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
    
    //fetch each to dict and add to arr
    while ((sqlite3_step(STMT)) == SQLITE_ROW) {
        
        NSString * ID         = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 0)];
        NSString * Doc_ID          = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 1)];
        NSString * Post_By          = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 2)];
        NSString * Talk_Text             = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 3)];
        NSString * TimeStamp     = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 4)];
        NSString * List_File_ID      = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 5)];
        NSString * List_File_Name      = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 6)];
        
        
        NSDictionary * dicForEachRow = [[NSDictionary alloc] initWithObjectsAndKeys:
                                        ID, @"ID",
                                        Doc_ID, @"Doc_ID",
                                        Post_By, @"Post_By",
                                        Talk_Text, @"Talk_Text",
                                        TimeStamp, @"TimeStamp",
                                        List_File_ID, @"List_File_ID",
                                        List_File_Name, @"List_File_Name",
                                        nil];
        [returnArr addObject:dicForEachRow];
    }
    
    [self closeDB];
    
    return returnArr;
}













-(BOOL)isHasEmpdataDB
{
    [self initialDB];
    //prepare statement
    const char * queryStatement = [@"SELECT * FROM ListEmp " UTF8String];
    sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
    BOOL chk=NO;
    while ((sqlite3_step(STMT)) == SQLITE_ROW) {
        chk=YES;
    }
    [self closeDB];
    return chk;
    
}

-(NSMutableArray*) getEmpdata
{
    [self initialDB];
    NSMutableArray * returnArr = [[NSMutableArray alloc] init];
    //prepare statement
    const char * queryStatement = [@"select email from ListEmp" UTF8String];
    sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
    //fetch each to dict and add to arr
    while ((sqlite3_step(STMT)) == SQLITE_ROW) {
        NSString *email              = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 0)];
        [returnArr addObject:email];
    }
    [self closeDB];
    return returnArr;
}
-(void)saveEmpData
{
    [self initialDB];
    
    //check internet access
    if([self internetConnected])
    {
        //at first delete old data
        NSString * strDeleteStatement = [NSString stringWithFormat:@"DELETE FROM ListEmp"];
        const char * consCharDeleteStatement = [strDeleteStatement UTF8String];
        sqlite3_prepare_v2(DB, consCharDeleteStatement, -1, &STMT, NULL);
        sqlite3_step(STMT);
        
        //get json into array
        NSMutableArray * arrOfDict = [[NSMutableArray alloc] init];
        
        //NSString *strURL=[NSString stringWithFormat:@"http://%@ListEmployee.ashx?Search=",serviceUrl];
        //NSData *json = [NSData dataWithContentsOfURL:[NSURL URLWithString:strURL]];
        /////////////////new
        EGAUtility * Util = [[EGAUtility alloc]init];
        NSString *strURL=[NSString stringWithFormat:@"ws=ListEmployee.ashx&Search=%@",@""];
        NSData *json =[Util getDataFromService:strURL];
        /////////////////new

        
        arrOfDict = [NSJSONSerialization JSONObjectWithData:json options:kNilOptions error:nil];
        
        //insert json ListAllTask to DB
        sqlite3_step(STMT);
        for(NSDictionary *dictForEachRow in arrOfDict)
        {
            NSString * email               = [dictForEachRow objectForKey:@"Email"];
            NSString * empID           = [dictForEachRow objectForKey:@"ID"];
            
            NSString * strInsertStatement = [NSString stringWithFormat:@"INSERT INTO ListEmp (email) VALUES ('%@')",email];
            
            const char * consCharInsertStatement = [strInsertStatement UTF8String];
            sqlite3_prepare_v2(DB, consCharInsertStatement, -1, &STMT, NULL);
            
            sqlite3_step(STMT);
        }
       
    }
    
    [self closeDB];
}












#pragma ListNewBook

-(BOOL)isHasNewBookDB
{
    [self initialDB];
    //prepare statement
    const char * queryStatement = [@"SELECT * FROM ListNewBook " UTF8String];
    sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
    BOOL chk=NO;
    while ((sqlite3_step(STMT)) == SQLITE_ROW) {
        chk=YES;
    }
    [self closeDB];
    return chk;
    
}
-(void)saveNewBook:(NSString*)Type
{
    
    [self initialDB];
    //check internet access
    if([self internetConnected])
    {
        //at first delete old data
        //        NSString * strDeleteStatement = [NSString stringWithFormat:@"DELETE FROM ListNewBook"];
        //        const char * consCharDeleteStatement = [strDeleteStatement UTF8String];
        //        sqlite3_prepare_v2(DB, consCharDeleteStatement, -1, &STMT, NULL);
        //        sqlite3_step(STMT);
        
        
        NSString *search=@"";
        NSString *upperID=@"";
        NSString *lowerID=@"";
        NSString *ID;
        if ([Type isEqualToString:@"upperID"])
        {
            ID=@"0";
            const char * queryStatement = [@"select Max(ID) from ListNewBook " UTF8String];
            sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
            
            //fetch each to dict and add to arr
            while ((sqlite3_step(STMT)) == SQLITE_ROW) {
                ID= [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 0)];
                //NSLog(@"Min ID  xx:%@", ID );
                
            }
            
            upperID=ID;
        } else if ([Type isEqualToString:@"lowerID"])
        {
            
            
            ID=@"0";
            const char * queryStatement = [@"select Min(ID) from ListNewBook " UTF8String];
            sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
            
            //fetch each to dict and add to arr
            while ((sqlite3_step(STMT)) == SQLITE_ROW) {
                ID= [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 0)];
                //NSLog(@"Min ID  xx:%@", ID );
                
            }
            lowerID=ID;
        }
        //get json into array
        NSMutableArray * arrOfDict = [[NSMutableArray alloc] init];
        //NSString *strURL=[NSString stringWithFormat:@"http://%@ListNewBook.ashx?search=%@&lowerID=%@&upperID=%@&chkAssign=0",serviceUrl,search,lowerID,upperID];
        
        //NSData *json = [NSData dataWithContentsOfURL:[NSURL URLWithString:strURL]];
        
        
        /////////////////new
        EGAUtility * Util = [[EGAUtility alloc]init];
        NSString *strURL=[NSString stringWithFormat:@"ws=ListNewBook.ashx&search=%@&lowerID=%@&upperID=%@&chkAssign=0",search,lowerID,upperID];
        NSData *json =[Util getDataFromService:strURL];
        /////////////////new
        
        
        arrOfDict = [NSJSONSerialization JSONObjectWithData:json options:kNilOptions error:nil];
        
        //insert json ListAllTask to DB
        for(NSDictionary *dictForEachRow in arrOfDict)
        {
            NSString * ID = [dictForEachRow objectForKey:@"ID"];
            NSString * Doc_FiscalYear = [dictForEachRow objectForKey:@"Doc_FiscalYear"];
            NSString * Doc_Register = [dictForEachRow objectForKey:@"Doc_Register"];
            NSString * Doc_Serial = [dictForEachRow objectForKey:@"Doc_Serial"];
            NSString * Doc_Subject = [dictForEachRow objectForKey:@"Doc_Subject"];
            NSString * Doc_Description = [dictForEachRow objectForKey:@"Doc_Description"];
            NSString * Doc_date = [dictForEachRow objectForKey:@"Doc_date"];
            NSString * Doc_ReceiveDate = [dictForEachRow objectForKey:@"Doc_ReceiveDate"];
            NSString * Doc_to = [dictForEachRow objectForKey:@"Doc_to"];
            NSString * DocToName = [dictForEachRow objectForKey:@"DocToName"];
            NSString * Org_Name = [dictForEachRow objectForKey:@"Org_Name"];
            NSString * Doc_Priority_ID = [dictForEachRow objectForKey:@"Doc_Priority_ID"];
            NSString * Doc_Priority_Name = [dictForEachRow objectForKey:@"Doc_Priority_Name"];
            NSString * Doc_Secret_ID = [dictForEachRow objectForKey:@"Doc_Secret_ID"];
            NSString * Doc_Status_ID = [dictForEachRow objectForKey:@"Doc_Status_ID"];
            NSString * Doc_Status_Name = [dictForEachRow objectForKey:@"Doc_Status_Name"];
            NSString * empEmail = [dictForEachRow objectForKey:@"empEmail"];
            NSString * endDate = [dictForEachRow objectForKey:@"endDate"];
            NSString * startDate = [dictForEachRow objectForKey:@"startDate"];
            NSString * taskDetail = [dictForEachRow objectForKey:@"taskDetail"];
            NSString * IsDueDate = [dictForEachRow objectForKey:@"IsDueDate"];
            NSString * DocPriorityID = [dictForEachRow objectForKey:@"DocPriorityID"];
            NSString * chkAssign = [dictForEachRow objectForKey:@"chkAssign"];
            NSString * DocBookBeforeSign_ID = [dictForEachRow objectForKey:@"DocBookBeforeSign_ID"];
            NSString * DocBookBeforeSign_Name = [dictForEachRow objectForKey:@"DocBookBeforeSign_Name"];
            NSString * DocAttachment_ID = [dictForEachRow objectForKey:@"DocAttachment_ID"];
            NSString * DocAttachment_Name = [dictForEachRow objectForKey:@"DocAttachment_Name"];

            NSString * strInsertStatement = [NSString stringWithFormat:@"INSERT INTO ListNewBook (ID, Doc_FiscalYear, Doc_Register, Doc_Serial, Doc_Subject, Doc_Description, Doc_date, Doc_ReceiveDate, Doc_to, DocToName, Org_Name, Doc_Priority_ID, Doc_Priority_Name, Doc_Secret_ID, Doc_Status_ID, Doc_Status_Name,empEmail,endDate,startDate,taskDetail,IsDueDate,DocPriorityID,chkAssign,DocBookBeforeSign_ID,DocBookBeforeSign_Name,DocAttachment_ID,DocAttachment_Name) VALUES ('%@', '%@','%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@')", ID, Doc_FiscalYear, Doc_Register, Doc_Serial, Doc_Subject, Doc_Description, Doc_date, Doc_ReceiveDate, Doc_to, DocToName, Org_Name, Doc_Priority_ID, Doc_Priority_Name, Doc_Secret_ID, Doc_Status_ID, Doc_Status_Name,empEmail,endDate,startDate,taskDetail,IsDueDate,DocPriorityID,chkAssign,DocBookBeforeSign_ID,DocBookBeforeSign_Name,DocAttachment_ID,DocAttachment_Name];
            const char * consCharInsertStatement = [strInsertStatement UTF8String];
            sqlite3_prepare_v2(DB, consCharInsertStatement, -1, &STMT, NULL);
            sqlite3_step(STMT);
        }
    }
    
    [self closeDB];
}
-(void)uddateBookList:(NSString*)documentListID
              empMail:(NSString*)empMail
          description:(NSString*)description
            StartDate:(NSString*)StartDate
              EndDate:(NSString*)EndDate
        DocPriorityID:(NSString*)DocPriorityID
            IsDueDate:(NSString*)IsDueDate
{
     [self deleteNewBookAssign:documentListID];
    
    [self initialDB];
    
   
        NSString* SQL = [NSString stringWithFormat:@"Update ListBook  set empEmail='%@',taskDetail='%@',startDate='%@',endDate='%@',DocPriorityID='%@',IsDueDate='%@',chkAssign='1' where ID='%@'",empMail,description,StartDate,EndDate,DocPriorityID,IsDueDate,documentListID];
    NSLog(@"systex%@",SQL);
    
    //prepare statement
    const char * queryStatement = [SQL UTF8String];
    //sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
    if(sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL) != SQLITE_OK)
    {
        NSAssert1(0, @"Error statement. '%s'", sqlite3_errmsg(DB));
    }
    
    if (sqlite3_step(STMT) == SQLITE_DONE)
    {
        NSLog(@"OK");
        
    } else {
        // NSLog(@"sqlite3_step error: '%s'", sqlite3_errmsg(DB));
        NSLog(@"Problem!!!");
        
    }
    
    sqlite3_step(STMT);
    [self closeDB];
    
}
-(void)saveBookStatus:(NSString*)DocID
{
    [self initialDB];
    
    NSString * strStatement = [NSString stringWithFormat:@"update ListNewBook set isOpen=1  WHERE ID = '%@'",DocID];
    const char * consCharDeleteStatement = [strStatement UTF8String];
    sqlite3_prepare_v2(DB, consCharDeleteStatement, -1, &STMT, NULL);
    sqlite3_step(STMT);
    
    [self closeDB];
}
-(NSMutableArray*) getNewBook
{
    [self initialDB];
    
    NSMutableArray * returnArr = [[NSMutableArray alloc] init];
    
    //prepare statement
    const char * queryStatement = [@"select * from ListNewBook order by ID desc" UTF8String];
    sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
    
    //fetch each to dict and add to arr
    while ((sqlite3_step(STMT)) == SQLITE_ROW) {
        NSString *ID                = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 0)];
        NSString *Doc_FiscalYear    = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 1)];
        NSString *Doc_Register      = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 2)];
        NSString *Doc_Serial        = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 3)];
        NSString *Doc_Subject       = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 4)];
        NSString *Doc_Description   = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 5)];
        NSString *Doc_date          = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 6)];
        NSString *Doc_ReceiveDate   = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 7)];
        NSString *Doc_to            = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 8)];
        NSString *DocToName         = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 9)];
        NSString *Org_Name          = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 10)];
        NSString *Doc_Priority_ID   = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 11)];
        NSString *Doc_Priority_Name = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 12)];
        NSString *Doc_Secret_ID     = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 13)];
        NSString *Doc_Status_ID     = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 14)];
        NSString *Doc_Status_Name   = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 15)];
        
        
        NSString * empEmail = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 16)];
        NSString * endDate =  [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 17)];
        NSString * startDate =  [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 18)];
        NSString * taskDetail =  [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT,19)];
        
        NSString * isOpen =  [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT,23)];
        
        NSString * DocBookBeforeSign_ID = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT,24)];
        NSString * DocBookBeforeSign_Name = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT,25)];
        NSString * DocAttachment_ID = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT,26)];
        NSString * DocAttachment_Name = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT,27)];
        
        //        NSString * IsDueDate =  [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 20)];
        //        NSString * DocPriorityID = [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 21)];
        //        NSString * chkAssign =  [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 22)];
        
        NSDictionary * dicForEachRow = [[NSDictionary alloc] initWithObjectsAndKeys:
                                        ID, @"ID",
                                        Doc_FiscalYear, @"Doc_FiscalYear",
                                        Doc_Register, @"Doc_Register",
                                        Doc_Serial, @"Doc_Serial",
                                        Doc_Subject, @"Doc_Subject",
                                        Doc_Description, @"Doc_Description",
                                        Doc_date, @"Doc_date",
                                        Doc_ReceiveDate, @"Doc_ReceiveDate",
                                        Doc_to, @"Doc_to",
                                        DocToName, @"DocToName",
                                        Org_Name, @"Org_Name",
                                        Doc_Priority_ID, @"Doc_Priority_ID",
                                        Doc_Priority_Name, @"Doc_Priority_Name",
                                        Doc_Secret_ID, @"Doc_Secret_ID",
                                        Doc_Status_ID, @"Doc_Status_ID",
                                        Doc_Status_Name, @"Doc_Status_Name",
                                        empEmail , @"empEmail",
                                        endDate ,  @"endDate",
                                        startDate ,  @"startDate",
                                        taskDetail ,  @"taskDetail",
                                        isOpen ,  @"isOpen",
                                        DocBookBeforeSign_ID ,  @"DocBookBeforeSign_ID",
                                        DocBookBeforeSign_Name ,  @"DocBookBeforeSign_Name",
                                        DocAttachment_ID ,  @"DocAttachment_ID",
                                        DocAttachment_Name ,  @"DocAttachment_Name",
                                        //                                         IsDueDate =   @"IsDueDate",
                                        //                                         DocPriorityID =  @"DocPriorityID",
                                        //                                         chkAssign =  @"chkAssign",
                                        
                                        
                                        
                                        nil];
        [returnArr addObject:dicForEachRow];
    }
    
    [self closeDB];
    
    return returnArr;
}
-(void)deleteNewBookAssign:(NSString*)DocID
{
    
    [self initialDB];
    
    
    
    NSString * strStatement = [NSString stringWithFormat:@"delete from ListNewBook  WHERE ID = '%@'",DocID];
    
    const char * consCharDeleteStatement = [strStatement UTF8String];
    
    sqlite3_prepare_v2(DB, consCharDeleteStatement, -1, &STMT, NULL);
    
    sqlite3_step(STMT);
    
    
    
    [self closeDB];
    
}
-(void) saveAllBookNew:(NSString*)Type
{
    
    [self initialDB];
    
    //check internet access
    
    if([self internetConnected])
        
    {
        
        
        
        //get json into array
        
        NSString *search=@"";
        
        NSString *upperID=@"";
        
        NSString *lowerID=@"";
        
        NSString *ID;
        
        NSString *countRow=@"0";
        
        if ([Type isEqualToString:@"upperID"])
            
        {
            
            const char * queryStatement = [@"select Max(ID) from ListNewBook " UTF8String];
            
            sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
            
            ID=@"";
            
            //fetch each to dict and add to arr
            
            while ((sqlite3_step(STMT)) == SQLITE_ROW) {
                
                ID= [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 0)];
                
            }
            
            upperID=ID;
            
            
            
            
            
        } else if ([Type isEqualToString:@"lowerID"])
            
        {
            
            
            
            const char * queryStatement = [@"select Min(ID) from ListBook " UTF8String];
            
            sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
            
            
            
            //fetch each to dict and add to arr
            
            while ((sqlite3_step(STMT)) == SQLITE_ROW) {
                
                ID= [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 0)];
                
            }
            
            lowerID=ID;
            
            
            
            
            
            //            queryStatement = [@"select count(ID) from ListNewBook " UTF8String];
            
            //            sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
            
            //            while ((sqlite3_step(STMT)) == SQLITE_ROW) {
            
            //                countRow= [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 0)];
            
            //            }
            
            
            
        }
        
        
        
        
        
        
        
        
        
        NSMutableArray * arrOfDict = [[NSMutableArray alloc] init];
        
        
        
       // NSString *strURL=[NSString stringWithFormat:@"http://%@ListNewBook.ashx?search=%@&lowerID=%@&upperID=%@&chkAssign=0&countRow=%@",serviceUrl,search,lowerID,upperID,countRow];
        
        
        
       // NSData *json = [NSData dataWithContentsOfURL:[NSURL URLWithString:strURL]];
        
        /////////////////new
        EGAUtility * Util = [[EGAUtility alloc]init];
        NSString *strURL=[NSString stringWithFormat:@"ws=ListNewBook.ashx&search=%@&lowerID=%@&upperID=%@&chkAssign=0&countRow=%@",search,lowerID,upperID,countRow];
        NSData *json =[Util getDataFromService:strURL];
        /////////////////new

        
        arrOfDict = [NSJSONSerialization JSONObjectWithData:json options:kNilOptions error:nil];
        
        NSInteger row=0;
        
        
        
        
        
        //        //at first delete old data
        
        //        NSString * strDeleteStatement = [NSString stringWithFormat:@"DELETE FROM ListBook"];
        
        //        const char * consCharDeleteStatement = [strDeleteStatement UTF8String];
        
        //        sqlite3_prepare_v2(DB, consCharDeleteStatement, -1, &STMT, NULL);
        
        //        sqlite3_step(STMT);
        
        
        
        for(NSDictionary *dictForEachRow in arrOfDict)
            
        {
            
            row=row+1;
            
            NSString * ID = [dictForEachRow objectForKey:@"ID"];
            
            NSString * Doc_FiscalYear = [dictForEachRow objectForKey:@"Doc_FiscalYear"];
            
            NSString * Doc_Register = [dictForEachRow objectForKey:@"Doc_Register"];
            
            NSString * Doc_Serial = [dictForEachRow objectForKey:@"Doc_Serial"];
            
            NSString * Doc_Subject = [dictForEachRow objectForKey:@"Doc_Subject"];
            
            NSString * Doc_Description = [dictForEachRow objectForKey:@"Doc_Description"];
            
            NSString * Doc_date = [dictForEachRow objectForKey:@"Doc_date"];
            
            NSString * Doc_ReceiveDate = [dictForEachRow objectForKey:@"Doc_ReceiveDate"];
            
            NSString * Doc_to = [dictForEachRow objectForKey:@"Doc_to"];
            
            NSString * DocToName = [dictForEachRow objectForKey:@"DocToName"];
            
            NSString * Org_Name = [dictForEachRow objectForKey:@"Org_Name"];
            
            NSString * Doc_Priority_ID = [dictForEachRow objectForKey:@"Doc_Priority_ID"];
            
            NSString * Doc_Priority_Name = [dictForEachRow objectForKey:@"Doc_Priority_Name"];
            
            NSString * Doc_Secret_ID = [dictForEachRow objectForKey:@"Doc_Secret_ID"];
            
            NSString * Doc_Status_ID = [dictForEachRow objectForKey:@"Doc_Status_ID"];
            
            NSString * Doc_Status_Name = [dictForEachRow objectForKey:@"Doc_Status_Name"];
            
            NSString * empEmail = [dictForEachRow objectForKey:@"empEmail"];
            
            NSString * endDate = [dictForEachRow objectForKey:@"endDate"];
            
            NSString * startDate = [dictForEachRow objectForKey:@"startDate"];
            
            NSString * taskDetail = [dictForEachRow objectForKey:@"taskDetail"];
            
            NSString * IsDueDate = [dictForEachRow objectForKey:@"IsDueDate"];
            
            NSString * DocPriorityID = [dictForEachRow objectForKey:@"DocPriorityID"];
            
            NSString * chkAssign = [dictForEachRow objectForKey:@"chkAssign"];
            
            NSString * DocBookBeforeSign_ID = [dictForEachRow objectForKey:@"DocBookBeforeSign_ID"];
            
            NSString * DocBookBeforeSign_Name = [dictForEachRow objectForKey:@"DocBookBeforeSign_Name"];
            
            NSString * DocAttachment_ID = [dictForEachRow objectForKey:@"DocAttachment_ID"];
            
            NSString * DocAttachment_Name = [dictForEachRow objectForKey:@"DocAttachment_Name"];

            
            
            //
            
            NSString * strInsertStatement = [NSString stringWithFormat:@"INSERT INTO ListBook (ID, Doc_FiscalYear, Doc_Register, Doc_Serial, Doc_Subject, Doc_Description, Doc_date, Doc_ReceiveDate, Doc_to, DocToName, Org_Name, Doc_Priority_ID, Doc_Priority_Name, Doc_Secret_ID, Doc_Status_ID, Doc_Status_Name,empEmail,endDate,startDate,taskDetail,IsDueDate,DocPriorityID,chkAssign,DocBookBeforeSign_ID,DocBookBeforeSign_Name,DocAttachment_ID,DocAttachment_Name) VALUES ('%@', '%@','%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@')", ID, Doc_FiscalYear, Doc_Register, Doc_Serial, Doc_Subject, Doc_Description, Doc_date, Doc_ReceiveDate, Doc_to, DocToName, Org_Name, Doc_Priority_ID, Doc_Priority_Name, Doc_Secret_ID, Doc_Status_ID, Doc_Status_Name,empEmail,endDate,startDate,taskDetail,IsDueDate,DocPriorityID,chkAssign,DocBookBeforeSign_ID,DocBookBeforeSign_Name,DocAttachment_ID,DocAttachment_Name];
            
            
            
            
            
            const char * consCharInsertStatement = [strInsertStatement UTF8String];
            
            // sqlite3_prepare_v2(DB, consCharInsertStatement, -1, &STMT, NULL);
            
            if(sqlite3_prepare_v2(DB, consCharInsertStatement, -1, &STMT, NULL) != SQLITE_OK)
                
            {
                
                NSAssert1(0, @"Error statement. '%s'", sqlite3_errmsg(DB));
                
            }
            
            
            
            if (sqlite3_step(STMT) == SQLITE_DONE)
                
            {
                
                //  NSLog(@"OK");
                
                
                
            } else {
                
                // NSLog(@"sqlite3_step error: '%s'", sqlite3_errmsg(DB));
                
                //  NSLog(@"Problem!!!");
                
                
                
            }
            
            
            
            sqlite3_step(STMT);
            
        }
        
    }
    
    
    
    [self closeDB];
    
}
-(void) saveAllBookListNew:(NSString*)Type
{
    
    [self initialDB];
    
    //check internet access
    
    if([self internetConnected])
        
    {
        
        //        //at first delete old data
        
        //        NSString * strDeleteStatement = [NSString stringWithFormat:@"DELETE FROM ListBook"];
        
        //        const char * consCharDeleteStatement = [strDeleteStatement UTF8String];
        
        //        sqlite3_prepare_v2(DB, consCharDeleteStatement, -1, &STMT, NULL);
        
        //        sqlite3_step(STMT);
        
        
        
        //get json into array
        
        
        
        NSString *search=@"";
        
        NSString *upperID=@"";
        
        NSString *lowerID=@"";
        
        NSString *ID;
        
        NSString *countRow=@"0";
        
        if ([Type isEqualToString:@"upperID"])
            
        {
            
            //const char * queryStatement = [@"select Max(ID) from ListBook " UTF8String];
            
            //sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
            
            ID=@"";
            
            //fetch each to dict and add to arr
            
            //while ((sqlite3_step(STMT)) == SQLITE_ROW) {
            
            //    ID= [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 0)];
            
            //}
            
            //upperID=ID;
            
            const char * queryStatement = [@"select Min(ID) from ListBook " UTF8String];
            
            sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
            
            
            
            //fetch each to dict and add to arr
            
            while ((sqlite3_step(STMT)) == SQLITE_ROW) {
                
                ID= [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 0)];
                
            }
            
            lowerID=ID;
            
            
            
            
            
            
            
        } else if ([Type isEqualToString:@"lowerID"])
            
        {
            
            
            
            const char * queryStatement = [@"select Min(ID) from ListBook " UTF8String];
            
            sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
            
            
            
            //fetch each to dict and add to arr
            
            while ((sqlite3_step(STMT)) == SQLITE_ROW) {
                
                ID= [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 0)];
                
            }
            
            lowerID=@"";
            
            queryStatement = [@"select Max(ID) from ListBook " UTF8String];
            
            sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
            
            
            
            //fetch each to dict and add to arr
            
            while ((sqlite3_step(STMT)) == SQLITE_ROW) {
                
                ID= [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 0)];
                
            }
            
            upperID=@"";
            
            
            
            
            
            
            
            
            
        }
        
        
        
        
        
        
        
        const char *queryStatement = [@"select count(ID) from ListBook " UTF8String];
        
        sqlite3_prepare_v2(DB, queryStatement, -1, &STMT, NULL);
        
        while ((sqlite3_step(STMT)) == SQLITE_ROW) {
            
            countRow= [NSString stringWithUTF8String:(char *)sqlite3_column_text(STMT, 0)];
            
        }
        
        
        
        NSMutableArray * arrOfDict = [[NSMutableArray alloc] init];
        
        //NSString *jsonURL=[NSString stringWithFormat:@"http://%@ListNewBook.ashx?search=",serviceUrl];
        
        
        
        //NSString *strURL=[NSString stringWithFormat:@"http://%@ListNewBook.ashx?search=%@&lowerID=%@&upperID=%@&chkAssign=2&countRow=%@&chkType=1",serviceUrl,search,lowerID,upperID,countRow];
        
        
        
        //NSData *json = [NSData dataWithContentsOfURL:[NSURL URLWithString:strURL]];
        
        
        /////////////////new
        EGAUtility * Util = [[EGAUtility alloc]init];
        NSString *strURL=[NSString stringWithFormat:@"ws=ListNewBook.ashx&search=%@&lowerID=%@&upperID=%@&chkAssign=2&countRow=%@&chkType=1",search,lowerID,upperID,countRow];
        NSData *json =[Util getDataFromService:strURL];
        /////////////////new

        
        
        arrOfDict = [NSJSONSerialization JSONObjectWithData:json options:kNilOptions error:nil];
        
        NSInteger row=0;
        
        
        
        
        
        //at first delete old data
        
        NSString * strDeleteStatement = [NSString stringWithFormat:@"DELETE FROM ListBook"];
        
        const char * consCharDeleteStatement = [strDeleteStatement UTF8String];
        
        sqlite3_prepare_v2(DB, consCharDeleteStatement, -1, &STMT, NULL);
        
        sqlite3_step(STMT);
        
        
        
        for(NSDictionary *dictForEachRow in arrOfDict)
            
        {
            
            row=row+1;
            
            NSString * ID = [dictForEachRow objectForKey:@"ID"];
            
            NSString * Doc_FiscalYear = [dictForEachRow objectForKey:@"Doc_FiscalYear"];
            
            NSString * Doc_Register = [dictForEachRow objectForKey:@"Doc_Register"];
            
            NSString * Doc_Serial = [dictForEachRow objectForKey:@"Doc_Serial"];
            
            NSString * Doc_Subject = [dictForEachRow objectForKey:@"Doc_Subject"];
            
            NSString * Doc_Description = [dictForEachRow objectForKey:@"Doc_Description"];
            
            NSString * Doc_date = [dictForEachRow objectForKey:@"Doc_date"];
            
            NSString * Doc_ReceiveDate = [dictForEachRow objectForKey:@"Doc_ReceiveDate"];
            
            NSString * Doc_to = [dictForEachRow objectForKey:@"Doc_to"];
            
            NSString * DocToName = [dictForEachRow objectForKey:@"DocToName"];
            
            NSString * Org_Name = [dictForEachRow objectForKey:@"Org_Name"];
            
            NSString * Doc_Priority_ID = [dictForEachRow objectForKey:@"Doc_Priority_ID"];
            
            NSString * Doc_Priority_Name = [dictForEachRow objectForKey:@"Doc_Priority_Name"];
            
            NSString * Doc_Secret_ID = [dictForEachRow objectForKey:@"Doc_Secret_ID"];
            
            NSString * Doc_Status_ID = [dictForEachRow objectForKey:@"Doc_Status_ID"];
            
            NSString * Doc_Status_Name = [dictForEachRow objectForKey:@"Doc_Status_Name"];
            
            
            
            NSString * empEmail = [dictForEachRow objectForKey:@"empEmail"];
            
            NSString * endDate = [dictForEachRow objectForKey:@"endDate"];
            
            NSString * startDate = [dictForEachRow objectForKey:@"startDate"];
            
            NSString * taskDetail = [dictForEachRow objectForKey:@"taskDetail"];
            
            NSString * IsDueDate = [dictForEachRow objectForKey:@"IsDueDate"];
            
            NSString * DocPriorityID = [dictForEachRow objectForKey:@"DocPriorityID"];
            
            NSString * chkAssign = [dictForEachRow objectForKey:@"chkAssign"];
            
            NSString * DocBookBeforeSign_ID = [dictForEachRow objectForKey:@"DocBookBeforeSign_ID"];
            
            NSString * DocBookBeforeSign_Name = [dictForEachRow objectForKey:@"DocBookBeforeSign_Name"];
            
            NSString * DocAttachment_ID = [dictForEachRow objectForKey:@"DocAttachment_ID"];
            
            NSString * DocAttachment_Name = [dictForEachRow objectForKey:@"DocAttachment_Name"];
            
            //
            
            NSString * strInsertStatement = [NSString stringWithFormat:@"INSERT INTO ListBook (ID, Doc_FiscalYear, Doc_Register, Doc_Serial, Doc_Subject, Doc_Description, Doc_date, Doc_ReceiveDate, Doc_to, DocToName, Org_Name, Doc_Priority_ID, Doc_Priority_Name, Doc_Secret_ID, Doc_Status_ID, Doc_Status_Name,empEmail,endDate,startDate,taskDetail,IsDueDate,DocPriorityID,chkAssign,DocBookBeforeSign_ID,DocBookBeforeSign_Name,DocAttachment_ID,DocAttachment_Name) VALUES ('%@', '%@','%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@')", ID, Doc_FiscalYear, Doc_Register, Doc_Serial, Doc_Subject, Doc_Description, Doc_date, Doc_ReceiveDate, Doc_to, DocToName, Org_Name, Doc_Priority_ID, Doc_Priority_Name, Doc_Secret_ID, Doc_Status_ID, Doc_Status_Name,empEmail,endDate,startDate,taskDetail,IsDueDate,DocPriorityID,chkAssign,DocBookBeforeSign_ID,DocBookBeforeSign_Name,DocAttachment_ID,DocAttachment_Name];
            
            
            
            
            
            const char * consCharInsertStatement = [strInsertStatement UTF8String];
            
            // sqlite3_prepare_v2(DB, consCharInsertStatement, -1, &STMT, NULL);
            
            if(sqlite3_prepare_v2(DB, consCharInsertStatement, -1, &STMT, NULL) != SQLITE_OK)
                
            {
                
                NSAssert1(0, @"Error statement. '%s'", sqlite3_errmsg(DB));
                
            }
            
            
            
            if (sqlite3_step(STMT) == SQLITE_DONE)
                
            {
                
                //  NSLog(@"OK");
                
                
                
            } else {
                
                // NSLog(@"sqlite3_step error: '%s'", sqlite3_errmsg(DB));
                
                //  NSLog(@"Problem!!!");
                
                
                
            }
            
            
            
            sqlite3_step(STMT);
            
        }
        
    }
    
    
    
    [self closeDB];
    
}















@end
